<?php


namespace app\admin\model;


use app\sys\model\ConnLog;
use app\sys\model\Hint;
use app\sys\model\UrlSourceLog;
use app\sys\service\GateCli;
use GatewayWorker\Lib\Gateway;
use think\Db;
use think\db\Query;
use think\Exception;
use think\facade\Cache;
use think\facade\Log;
use think\facade\Request;
use think\Model;

class Chat extends Model
{
    protected $autoWriteTimestamp = true;

    /**
     * 是vip用户  user_mobile 不为空
     */
    const IS_VIP_YES = 1;
    /**
     * 不是vip用户  user_mobile 为空
     */
    const IS_VIP_NO = -1;

    /**
     * @param $u
     * @param $user
     * @param $staff
     * @param $s
     * @param $agent
     * @param $data
     * @param $sendToNo
     * @return array|string
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\ModelNotFoundException
     * @throws \think\exception\DbException
     * 添加会话
     */
    public static function add($u, $user, $staff, $s, $agent, $data, $sendToNo)
    {
        $type = 0;
        $createDay = strtotime(date("Y-m-d"), time());
        $createMon = strtotime(date("Y-m"), time());
        // 房间
        $resChat = Db::table("ic_chat")->where("user_id", $user)->where("staff_id", $staff)->find();
        if (!$resChat) {
            $arr = [
                "user_id" => $user,
                "user_no" => $u["no"],
                "user_name" => $u["name"],
                "user_icon" => $u["icon"],
                "user_mobile" => $u["mobile"],
                "staff_id" => $staff,
                "staff_no" => $s["staff_no"],
                "staff_name" => $s["staff_name"],
                "staff_id_log" => $staff,
                "user_agent" => $agent,
                "last_info" => $data,
                "send_to" => $sendToNo,
                "last_info_status" => 0,
                "create_day" => $createDay,
                "create_mon" => $createMon
            ];
            $chatR = Chat::create($arr);
            if ($chatR->id == 0) {
                return "添加失败";
            }
            $id = $chatR->id;
            $type = 1;

            // 第一次聊天 (自动回复)

        } else {
            $id = $resChat["id"];
            $saveChat["last_info"] = $data;
            $saveChat["send_to"] = $sendToNo;
            $saveChat["last_info_status"] = 0;
            $saveChat["update_time"] = time();
            Chat::where("id", $id)->data($saveChat)->update();
        }
        RobotSet::openWind($u["no"], $s["staff_no"]);//非首次聊天触发自动回复
        return [$id, $type];
    }

    /**
     * 自动回复
     * @param $user
     * @param $staff
     * @param $data
     * @param $isMen
     * @return string
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\ModelNotFoundException
     * @throws \think\exception\DbException
     */
    public static function TimerToUser($user, $staff, $data, $isMen): string
    {
        $u = Db::table("ic_user")->where("id", $user)->find();
        if (empty($u)) {
            return "用户不存在";
        }
        $s = Db::table("ic_staff")->where("id", $staff)->find();
        if (!$s) {
            return "客服不存在";
        }

        $toUidNo = "use_" . $u["no"];
        $seUidNo = "sta_" . $s["staff_no"];

        $sendToNo = "user";
        // 添加会话
        list($id, $type) = Chat::add($u, $user, $staff, $s, "", $data, $sendToNo);

        if ($type == 0) {

            // 添加记录
            $chatLog = ChatLog::add(0, $id, $user, $staff, $data, "", $sendToNo, $seUidNo, $s["staff_no"], $u["no"]);

            //获取信息
            $list = ChatLog::alias('cl')
                ->field("
             IFNULL(ss.url,'') as store_url,IFNULL(ss.title,'') as store_title,IFNULL(ss.img,'') as store_img,IFNULL(ss.address,'') as store_address,
            IFNULL(ss.industry,'') as store_industry,IFNULL(ss.cost,'') as store_cost,IFNULL(ss.pv,'') as store_pv,IFNULL(ss.rent,'') as store_rent,
            IFNUll(ss.square,'') as store_square")
                ->leftJoin("ic_user_pv_log ss", "ss.id = cl.store_id")
                ->where("cl.chat_id", $id)->order("cl.id desc")
                ->find()->toArray();
            $js = array(
                "chat_type" => 0,
                'type' => "send",
                'client_id' => '',
                'chat_id' => $id,
                "chat_log_id" => $chatLog["id"],
                'to_user_no' => $toUidNo,
                'send_user_no' => $seUidNo,
                "info" => $data,
                "send_to" => $sendToNo,
                "user_type" => $isMen // 会员类型
            );

            $sendInfo = array_merge($js, $list);
            $mess = wsJson("success", $sendInfo);
            Log::info("TimerToUser:{$toUidNo}-{$mess},".date("Y-m-d H:i:s"));
            // 对应端口号
            Gateway::$registerAddress = \think\facade\Env::get("GATEWAY.REGIS_ADDRESS");
            Gateway::sendToUid($toUidNo, $mess);
            Gateway::sendToUid($seUidNo, $mess);
        }
        return "";
    }

    /**
     * 对话提示语
     * @param $user
     * @param $staff
     * @param int $isMen
     * @param string $sendType
     * @return string
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\ModelNotFoundException
     * @throws \think\exception\DbException
     */
    public static function TisToUser($user, $staff, $isMen = 0, $sendType = ""): string
    {
        $u = Db::table("ic_user")->where("id", $user)->find();
        if (empty($u)) {
            Log::info("TisToUser:用户不存在");
            return "";
        }
        if ($u && $isMen == 0) {
            $isMen = User::getIsMen($u["mobile"]);
        }
        $s = Db::table("ic_staff")->where("id", $staff)->find();
        if (!$s) {
            Log::info("TisToUser:客服不存在");
            return "";
        }
        // 发送标识
        $toUidNo = "use_" . $u["no"];
        $seUidNo = "sta_" . $s["staff_no"];

        // 提示语
        $hitData = Hint::field("text,is_time")->where("chat_name", $sendType)->where("status", 1)->find();
        if (!$hitData) {
            Log::info("TisToUser:提示语已关闭");
            return "";
        }
        $data = $hitData["text"];

        if ($hitData["is_time"]) {
            $data = $data . " " . date("Y-m-d H:i:s");
        }

        Log::info("TisToUser发送:{$data}");
        // 对话提示语
        $id = Db::table("ic_chat")->where("user_id", $user)->where("staff_id", $staff)->value("id");

        if ($id) {

            // 记录
            $chatLog = ChatLog::addLes(0, $id, $user, $staff, $data, "", "sys", $seUidNo, $s["staff_no"], $u["no"]);

            //获取信息
            $list = ChatLog::alias('cl')
                ->field("
             IFNULL(ss.url,'') as store_url,IFNULL(ss.title,'') as store_title,IFNULL(ss.img,'') as store_img,IFNULL(ss.address,'') as store_address,
            IFNULL(ss.industry,'') as store_industry,IFNULL(ss.cost,'') as store_cost,IFNULL(ss.pv,'') as store_pv,IFNULL(ss.rent,'') as store_rent,
            IFNUll(ss.square,'') as store_square,cl.create_time")
                ->leftJoin("ic_user_pv_log ss", "ss.id = cl.store_id")
                ->where("cl.chat_id", $id)->order("cl.id desc")
                ->find()->toArray();
            $js = array(
                "chat_type" => 0,
                'type' => "sys",
                'client_id' => '',
                'chat_id' => $id,
                "chat_log_id" => $chatLog["id"],
                'to_user_no' => $toUidNo,
                'send_user_no' => $seUidNo,
                "info" => $data,
                "sys_time" => "",
                "send_to" => "all",
                "user_type" => $isMen // 会员类型
            );
            // 是否开启时间
            if ($hitData["is_time"]) {
                $js["sys_time"] = date("Y-m-d H:i:s");
            }

            $sendInfo = array_merge($js, $list);

//            // 是否开启时间
//            if($hitData["is_time"] && $sendInfo["create_time"]){
//                $sendInfo["info"] = $sendInfo["info"].$sendInfo["create_time"];
//            }
            $mess = wsJson("success", $sendInfo);
            Log::info("TisToUser:{$toUidNo}-{$mess}");
            // 对应端口号
            Gateway::$registerAddress = \think\facade\Env::get("GATEWAY.REGIS_ADDRESS");
            Gateway::sendToUid($toUidNo, $mess);
            Gateway::sendToUid($seUidNo, $mess);
        } else {
            Log::info("TisToUser:提示失败,房间不存在");
        }
        return "";
    }

    //
    public static function robotStoreToUser($userNo, $mobile, $staffNo, $pieds)
    {
//        $u = Db::table("ic_user")->where("no", $userNo)->find();
//        if (empty($u)) {
//            //判断是会员还是游客
//            list($userId, $isMen) = User::addChatUser($data,$staffNo, Request::ip(),"");
////            Log::info("robotToUser:用户不存在");
////            return "";
//            $u
//        }
//        $isMen = 0;
//        if ($u) {
//            $isMen = User::getIsMen($u["mobile"]);
//        }
        $s = Db::table("ic_staff")->where("staff_no", $staffNo)->find();
        if (!$s) {
            Log::info("robotToUser:客服不存在");
            return "";
        }

        $data["user_no"] = $userNo;
        $data["mobile"] = $mobile;
        $data["duty_id"] = $s["duty_id"];
        $data["server_type"] = serverType($s["duty_id"]);
        list($userId, $isMen) = User::addChatUser($data, $staffNo, Request::ip(), "");
        $u = Db::table("ic_user")->where("id", $userId)->find();


        // 发送标识
        $toUidNo = "use_" . $u["no"];
        $seUidNo = "sta_" . $s["staff_no"];

        // 对话提示语
        $sendToNo = "user";
        // 添加会话
        list($id, $type) = Chat::add($u, $u["id"], $s["id"], $s, "", "", $sendToNo);
        $res = PushStore::getStore($pieds)->toArray();
        $res = array_column($res, null, 'store_id');
        $res_key = array_column($res, 'store_id');

        $redis = Cache::store('default')->handler();
        $old_temp = $redis->get('push_' . $userNo) ? $redis->get('push_' . $userNo) : '';
        if (!empty($old_temp)) {
            $old_redis = json_decode($old_temp, true);
            $now_res = array_diff($res_key, $old_redis);
        } else {
            $now_res = $res_key;
            $old_redis = [];
        }

        $count_res = count($res);
        $count_now_res = count($now_res);
        if ($count_res <= 11) {
            $now_temp = $res_key;
            $temp = array_keys($res_key);
        } else if ($count_now_res <= 11) {
            $now_temp = $res_key;
            $temp = array_rand($res_key, 11);
        } else {
            $now_temp = $now_res;
            $temp = array_rand($now_res, 11);
        }

        $cache_data = [];
        foreach ($temp as $v) {
            if ($id) {
                $js = array(
                    'type' => "robot",
                    'client_id' => '',
                    'chat_id' => $id,
                    "chat_log_id" => 0,
                    'to_user_no' => $toUidNo,
                    'send_user_no' => $seUidNo,
                    "info" => "",
                    "sys_time" => "",
                    "send_to" => "user",
                    "create_time" => date("Y-m-d H:i:s"),
                    "chat_type" => 1,
                    "user_type" => $isMen // 会员类型
                );

                $sendInfo = array_merge($js, $res[$now_temp[$v]]);
                $mess = wsJson("success", $sendInfo);
                Log::info("TisToUser:{$toUidNo}-{$mess}");
                // 对应端口号
                Gateway::$registerAddress = \think\facade\Env::get("GATEWAY.REGIS_ADDRESS");
                Gateway::sendToUid($toUidNo, $mess);
                $cache_data[] = $now_temp[$v];
                sleep(1);
            } else {
                Log::info("robotToUser:提示失败,房间不存在");
            }
        }

        $redis->set('push_' . $userNo, json_encode(array_unique(array_merge($old_redis, $cache_data))), 300);

        // 店铺匹配
        if (count($res) == 0) {
            RobotSet::matchStore($u["no"], $s["staff_no"], "");
        } else {
            RobotSet::matchStoreMore($u["no"], $s["staff_no"], "");
        }

        return "";
    }

    //消息
    public static function robotQuToUser($userNo, $staffNo, $quId)
    {
        $data = Qu::where("id", $quId)->value("text");
        if (!$data) {
            Log::info("robotQuToUser:消息不存在");
            return "消息不存在~";
        }
        $u = Db::table("ic_user")->where("no", $userNo)->find();
        if (empty($u)) {
            Log::info("robotQuToUser:用户不存在");
            return "";
        }
        $isMen = 0;
        if ($u) {
            $isMen = User::getIsMen($u["mobile"]);
        }
        $s = Db::table("ic_staff")->where("staff_no", $staffNo)->find();
        if (!$s) {
            Log::info("robotQuToUser:客服不存在");
            return "";
        }
        // 发送标识
        $toUidNo = "use_" . $u["no"];
        $seUidNo = "sta_" . $s["staff_no"];

        // 对话提示语
        $sendToNo = "user";
        // 添加会话
        list($id, $type) = Chat::add($u, $u["id"], $s["id"], $s, "", $data, $sendToNo);

        if ($id) {
            // 记录
            $chatLog = ChatLog::addLes(0, $id, $u["id"], $s["id"], $data, "", "user", $seUidNo, $s["staff_no"], $u["no"]);
            //获取信息
            $list = ChatLog::alias('cl')
                ->field("
             IFNULL(ss.url,'') as store_url,IFNULL(ss.title,'') as store_title,IFNULL(ss.img,'') as store_img,IFNULL(ss.address,'') as store_address,
            IFNULL(ss.industry,'') as store_industry,IFNULL(ss.cost,'') as store_cost,IFNULL(ss.pv,'') as store_pv,IFNULL(ss.rent,'') as store_rent,
            IFNUll(ss.square,'') as store_square,cl.create_time")
                ->leftJoin("ic_user_pv_log ss", "ss.id = cl.store_id")
                ->where("cl.chat_id", $id)->order("cl.id desc")
                ->find()->toArray();
            $js = array(
                "chat_type" => 0,
                'type' => "robot",
                'client_id' => '',
                'chat_id' => $id,
                "chat_log_id" => $chatLog["id"],
                'to_user_no' => $toUidNo,
                'send_user_no' => $seUidNo,
                "info" => $data,
                "sys_time" => "",
                "send_to" => "user",
                "user_type" => $isMen // 会员类型
            );

            $sendInfo = array_merge($js, $list);

            $mess = wsJson("success", $sendInfo);
            Log::info("robotQuToUser:{$toUidNo}-{$mess}");
            // 对应端口号
            Gateway::$registerAddress = \think\facade\Env::get("GATEWAY.REGIS_ADDRESS");
            Gateway::sendToUid($toUidNo, $mess);
        } else {
            Log::info("robotQuToUser:提示失败,房间不存在");
        }
        return "";
    }


    /**
     * @param $chatId
     * @param $staffNo
     * @param $collect
     * @return string
     * 标星
     */
    public static function makeSign($chatId, $staffNo, $collect)
    {
        $chat = Chat::where("id", $chatId)->where("staff_no", $staffNo)->find();
        if (!$chat) {
            return "数据不存在~";
        }
        $res = $chat->data(["collect" => $collect])->save();
        if (!$res) {
            return "添加失败~";
        }
        return "";
    }

    /**
     * @param $name
     * @param $info
     * @param array $date_arr
     * @param $page
     * @param $limit
     * @return array
     */
    public static function ChatList($pid, $pm, $name, $info, $date_arr = [], $page = 0, $limit = 10, $export = false)
    {

//        $sqlPv = UserPvLog::field("id,shop_phone,pxs_id")->group("pxs_id")->order("id desc")->buildSql();

        $sqlN = "( SELECT `id`,shop_phone,pxs_id,url FROM  ic_user_pv_log )";
        $userSql = Chat::alias("c")
            ->field("
            c.id,c.user_name,c.create_time,c.update_time,c.staff_no_log,c.staff_name,
            SUBSTRING_INDEX(c.staff_no_log, ',', -1) as  before_staff_no,
            pl.pxs_id,pl.url")
            ->leftJoin("ic_chat_log cl", "cl.chat_id = c.id")
            ->leftJoin($sqlN . " pl", "pl.id = cl.store_id");
        $userSql = Duty::getQueryByDataPermId($userSql, "c.staff_no");
        $userSql2 = Chat::alias("c")
            ->field("c.id,c.user_name,c.create_time,c.update_time")
            ->leftJoin("ic_chat_log cl", "cl.chat_id = c.id")
            ->leftJoin($sqlN . " pl", "pl.id = cl.store_id");
        $userSql2 = Duty::getQueryByDataPermId($userSql2, "c.staff_no");


        if ($pid) {

//            $sqlN = "( SELECT `id` FROM  ic_user_pv_log where pxs_id = ".$pid." )";
//            $resData = Db::query($sqlN);

//            print_r($resData);exit();
//            $userSql = $userSql->where("cl.store_id",  "in",$resData);
//            $userSql2 = $userSql2->where("cl.store_id", "in",$resData);

            $userSql = $userSql->where("pl.pxs_id", $pid);
            $userSql2 = $userSql2->where("pl.pxs_id", $pid);
        }
        if ($pm) {
            $userSql = $userSql->where("pl.shop_phone", $pm);
            $userSql2 = $userSql2->where("pl.shop_phone", $pm);
        }

        if ($name) {
            $userSql = $userSql->whereLike("c.user_name", "%" . $name . "%");
            $userSql2 = $userSql2->whereLike("c.user_name", "%" . $name . "%");
        }
        if ($info) {
            $userSql = $userSql->whereLike("cl.info", "%" . $info . "%");
            $userSql2 = $userSql2->whereLike("cl.info", "%" . $info . "%");
        }

        if ($date_arr) {

            $userSql = $userSql->whereBetween("c.create_time", $date_arr);
            $userSql2 = $userSql2->whereBetween("c.create_time", $date_arr);
        }

        // group 用 count 统计 容易出错
        if ($export) {
            $userSql = $userSql->where('cl.store_id', "<>", "")->group("cl.store_id,cl.chat_id");
        }
        if (!$export) $userSql = $userSql->group("c.id");
        $userSql2 = $userSql2->group("c.id");

        $resC = $userSql2->count();
        # 如果存在导出excel导出全部数据
        if ($export) {
            $res = $userSql->order("c.id desc")->select()->map(function ($list) {
                $list->before_staff_name = !empty($list->before_staff_no) ?
                    Staff::where("staff_no",$list->before_staff_no)->find()->staff_name
                    :
                    $list->staff_name;
                return $list;
            });
        }
        if (!$export) $res = $userSql->page($page, $limit)->order("c.id desc")->select();
        return listDate($res, $resC);
    }

    /**
     * @param $chatId
     * @param array $date_arr
     * @param int $page
     * @param int $limit
     * @return array
     */
    public static function chatLogList($chatId, $date_arr = [], $page = 0, $limit = 10)
    {
        $logSql = ChatLog::alias("a")
            ->field("a.*,IFNULL(ss.url,'') as store_url,IFNULL(ss.title,'') as store_title,IFNULL(ss.img,'') as store_img,IFNULL(ss.address,'') as store_address,
            IFNULL(ss.industry,'') as store_industry,IFNULL(ss.cost,'') as store_cost,IFNULL(ss.pv,'') as store_pv,IFNULL(ss.rent,'') as store_rent,
            IFNUll(ss.square,'') as store_square")
            ->leftJoin("ic_user_pv_log ss", "ss.id = a.store_id")
            ->where("a.chat_id", $chatId);
        if ($date_arr) {
            $logSql->whereBetween("a.create_time", $date_arr);
        }

        $res = $logSql->page($page, $limit)->order("a.create_time desc")->select();
        $resC = $logSql->count();
        return listDate($res, $resC);
    }


    public static function chatTj($site, $date_arr = [], $page = 0, $limit = 10)
    {
        $a = "ic_" . time() . mt_rand(10, 100);
        $allList = [];
        $List = [];
        $count = 0;

        if (!isset($date_arr[0]) || !isset($date_arr[1])) {
            return listTJDate($allList, $List, $count);
        }

        $sql = sprintf(/** @lang text */ "CREATE TABLE %s (
            id INT(11) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
            data_time INT(11) NOT NULL DEFAULT '0'
            )", $a);

        $strSql = "";
        // 添加数据
        if ($date_arr) {
            $str = "";
            $daArr = getDateFromRange($date_arr[0], $date_arr[1]);
            foreach ($daArr as $k => $v) {
                $vTime = strtotime($v);
                $str .= "($vTime),";

            }
            $strSql = substr($str, 0, strlen($str) - 1);
        }

        $delSql = sprintf(/** @lang text */ "DROP TABLE %s", $a);
        Db::startTrans();
        try {
            // 创建表
            Db::query($sql);

            // 添加数据
            if ($strSql) {
                $dataSql = sprintf(/** @lang text */ "INSERT INTO %s (data_time)
                                VALUES %s", $a, $strSql);
                Db::query($dataSql);
            }
            //
            $dataTimeArr = [];
            //            // 时间转换
            if (count($date_arr) > 0) {
                $dataTimeArr = [strtotime($date_arr[0]), strtotime($date_arr[1])];
            }

            // 客户对话次数
            $chatCountSql = ChatLog::field("*")->where("send_to", "staff");
            if (count($dataTimeArr) > 0) {
                $chatCountSql = $chatCountSql->whereBetween("create_day", $dataTimeArr);
            }
            if ($site) {
                $chatCountSql = $chatCountSql->whereBetween("site_value", $site);
            }
            $chatCountSql = $chatCountSql->group("create_day,chat_log_count,user_id")->buildSql();
            $chatCountSqlAl = Db::table($chatCountSql)
                ->alias("a")->field(" count(*) as chat_count,sum(if(is_valid = 1,1,0)) as valid_chat_count,create_day")->group("create_day")->buildSql();

            // 消息数
            $chatLogSql = ChatLog::field("sum(if(send_to = 'staff',1,0)) as chat_user_count,sum(if(send_to = 'user',1,0)) as chat_staff_count,create_day");
            if (count($dataTimeArr) > 0) {
                $chatLogSql = $chatLogSql->whereBetween("create_day", $dataTimeArr);
            }
            if ($site) {
                $chatLogSql = $chatLogSql->whereBetween("site_value", $site);
            }
            $chatLogSql = $chatLogSql->group("create_day")->buildSql();

            // 发起 (以客户第一次连上 谁先发送)
            $chatLogTimeSql = ChatLog::field("create_day,staff_id,send_to")->where("create_day", ">", 0)
                ->whereBetween("create_day", $dataTimeArr)
                ->group("create_day ,user_id")->buildSql();


            $chatSend = Db::table($chatLogTimeSql)->alias("d")->field("sum(if(d.send_to = 'staff',1,0)) as user_q,sum(if(d.send_to = 'user',1,0)) as staff_q,sum(if(d.send_to = 'robot',1,0)) as robot_q,d.create_day")
                ->group("d.create_day")->buildSql();


            // 访客无应答
            $chatLogStaffIsReplySql = ChatLog::field("create_day,send_to,is_reply")
                ->where("create_day", ">", 0)->whereBetween("create_day", $dataTimeArr)
                ->group("create_day,staff_id")->buildSql();
            $user_no_replySql = Db::table($chatLogStaffIsReplySql)->alias("c")->field("sum(if(is_reply = 0,1,0)) as user_no_reply,create_day")
                ->group("create_day")->buildSql();


            // 客服无应答
            $chatLogUserIsReplySql = ChatLog::field("create_day,send_to,is_reply")
                ->where("create_day", ">", 0)->whereBetween("create_day", $dataTimeArr)
                ->group("create_day,user_id")->buildSql();
            $staff_no_replySql = Db::table($chatLogUserIsReplySql)->alias("e")->field("sum(if(is_reply = 0,1,0)) as staff_no_reply,create_day")
                ->group("create_day")->buildSql();
            // 查看

            $data = Db::table($a)->alias("t")
                ->field("t.data_time,sum(st.time_long) as online_long,sum(sct.time_long) as chat_long, (sum(sct.time_long)/c.chat_count)as av_long ,
                c.chat_count,c.valid_chat_count,cl.chat_user_count,cl.chat_staff_count,0 as robot_log,s.user_q,s.staff_q,s.robot_q,urs.user_no_reply,srs.staff_no_reply")
                ->leftJoin("ic_staff_time st", "st.create_day = t.data_time")
                ->leftJoin("ic_staff_chat_time sct", "sct.create_day = t.data_time")
                ->leftJoin("{$chatCountSqlAl} c", "c.create_day =  t.data_time")
                ->leftJoin("{$chatLogSql} cl", "cl.create_day =  t.data_time")
                ->leftJoin("{$chatSend} s", "s.create_day = t.data_time")
                ->leftJoin("{$user_no_replySql} urs", "urs.create_day = t.data_time")
                ->leftJoin("{$staff_no_replySql} srs", "srs.create_day = t.data_time")
                ->group("t.data_time");
            $dataSql = $data->buildSql();

            //
            $allList = Db::table($dataSql)->alias("a")->field("sum(online_long) as online_long,sum(chat_long) as chat_long,sum(chat_long)/sum(chat_count) as av_long,
                            sum(chat_count) as chat_count,sum(valid_chat_count) as valid_chat_count,sum(chat_user_count) as chat_user_count,sum(chat_staff_count) as chat_staff_count,
                            sum(robot_log) as robot_log,
                            sum(user_q) as user_q,sum(staff_q) as staff_q,sum(robot_q)as robot_q,sum(user_no_reply) as user_no_reply,sum(staff_no_reply) as staff_no_reply")->select();
            $List = Db::table($dataSql)->alias("a")->page($page, $limit)->select();
            $count = Db::table($dataSql)->alias("a")->count();

//            // 删除表
            Db::query($delSql);
            Db::commit();
            return listTJDate($allList, $List, $count);
        } catch (\Exception $exception) {
            Db::query($delSql);
            Db::rollback();
            return listTJDate($allList, $List, $count);
        }
    }


    /**
     * @param $sendTo
     * @param $userId
     * @param $staffId
     * 回复状态修改
     */
    public static function UpIsReply($sendTo, $userId, $staffId)
    {
        if ($sendTo == "user") {
            ChatLog::where("user_id", $userId)->where("staff_id", $staffId)->where("send_to", "staff")->data(["is_reply" => 1])->update();
        } else if ($sendTo == "staff") {
            ChatLog::where("user_id", $userId)->where("staff_id", $staffId)->where("send_to", "user")->data(["is_reply" => 1])->update();
        }
    }

    /**
     * @param $staffNo
     * @param $userNo
     * @return string
     * 修改为有价值
     */
    public static function upIsValid($staffNo, $userNo)
    {
        $resChat = Chat::where("staff_no", $staffNo)->where("user_no", $userNo)->find();
        if ($resChat) {
            Chat::where("id", $resChat["id"])->data(["is_valid" => 1, "chat_valid_count" => $resChat["chat_valid_count"] + 1])->update();
            $log = ChatLog::where("chat_id", $resChat["id"])->order("create_time desc")->find();
            if ($log) {
                ChatLog::where("chat_id", $resChat["id"])
                    ->where("chat_log_count", $log["chat_log_count"])
                    ->where("conn_count", $log["conn_count"])->data(["is_valid" => 1])->update();
            }
        }
        return "";

    }

    /**
     * 今日概览
     */
    public static function newDay()
    {
        $adTime = strtotime(date("Y-m-d"));
        // 今日访客
        $chatCount = ConnLog::where("create_day", $adTime)->where("type", 0)->group('no')->count();
        // 客服在线
        $staffOnlineCount = Staff::where("status", "<", 3)->count();

        //  与 有效对话
        $chatValidDataSql = ChatLog::field(" if (sum(is_valid) > 0 ,1,0) as chat_valid_count,create_day,talk_no")->where("create_day", $adTime)->group("talk_no")->buildSql();
        $chatData = Db::table($chatValidDataSql)->alias("a")
            ->field("sum(chat_valid_count) as chat_valid_count,create_day,count(talk_no) as chat_count")->group("a.create_day")->find();
        //今日对话人数
        $chatPop = Db::name("chat")->whereBetweenTime("update_time", $adTime)->group("user_id")->count();
//        list($s,$d) = GetStrToTime([date("Y-m-d"),date("Y-m-d")]);
//        $chatPopSql = ChatLog::field("user_id,create_day,create_time")->whereTime("create_time", [$s, $d]);
//        $chatPopSql = $chatPopSql->group("user_id")->buildSql();
//        $chatPop = Db::table($chatPopSql)->alias("a")->field("count(*) as chat_pop,a.create_day")->count();
        // 对话时长z
        $chatTimeLong = StaffChatTime::where("create_day", $adTime)->sum("time_long");
        // 最佳对话
        $field = "(if (sum(if(send_to = 'staff',1,0)) >= 10 && sum(if(send_to = 'user',1,0)) >= 10,1,0) )as staff_chat_goods_count";
        $chatLog = ChatLog::field($field)->where("create_day", $adTime)->group("user_id,talk_no")->buildSql();
        $chatLog2 = Db::table($chatLog)->alias("a")->field("sum(staff_chat_goods_count) as staff_chat_goods_count")->find();
        // 3分钟
        $replySql = StaffReplyTime::field("(sum(if(time_second_long <= 180 and time_second_long > 0,1,0))/count(*)) as the_lv")->where("create_day", $adTime)->find();

        return [
            "pop_count" => (integer)$chatCount, "staff_online_count" => (integer)$staffOnlineCount,
            "chat_pop" => (integer)$chatPop, "chat_count" => (integer)$chatData["chat_count"],
            "chat_valid_count" => (integer)$chatData["chat_valid_count"], "chat_time_long" => (integer)$chatTimeLong,
            "staff_chat_goods_count" => (integer)$chatLog2["staff_chat_goods_count"], "the_lv" => $replySql["the_lv"] * 100
        ];
    }

    // 运营
    public static function operate($dateArr = [])
    {
        list($s, $d) = GetStrToTime($dateArr);
        // 今日对话人数
        $chatPopSql = ChatLog::field("user_id,create_day,create_time");
        // 与 有效对话
        $chatValidDataSql = ChatLog::field(" if (sum(is_valid) > 0 ,1,0) as chat_valid_count,create_day,create_time")->group("talk_no");


        // 最佳对话
        $field = "(if (sum(if(send_to = 'staff',1,0)) > 10 && sum(if(send_to = 'user',1,0)) > 10,1,0) )as staff_chat_goods_count,create_day";
        $chatLog = ChatLog::field($field);

        if ($dateArr) {
            $chatPopSql = $chatPopSql->whereTime("create_time", [$s, $d]);
            $chatValidDataSql = $chatValidDataSql->whereTime("create_time", [$s, $d]);
            $chatLog = $chatLog->whereTime("create_time", [$s, $d]);
        }

        $chatPopSql = $chatPopSql->group("create_day,user_id")->buildSql();
        $chatPopSql2 = Db::table($chatPopSql)->alias("a")->field("count(*) as chat_pop,a.create_day")->group("create_day,a.create_day")->buildSql();


        $chatValidDataSql2 = Db::table($chatValidDataSql->buildSql())->alias("a")
            ->field("count(*) as chat_count,sum(chat_valid_count) as chat_valid_count,create_day")->group("a.create_day");
        $chatValidDataSql2 = $chatValidDataSql2->buildSql();

        $chatLog = $chatLog->group("user_id,talk_no")->buildSql();
        $chatLog2 = Db::table($chatLog)->alias("a")
            ->field("sum(staff_chat_goods_count) as staff_chat_goods_count,create_day")->group("create_day")->buildSql();

        $res = Db::table($chatValidDataSql2)->alias("b")->field("c.staff_chat_goods_count,b.create_day,d.chat_pop,b.chat_valid_count")
            ->leftJoin($chatLog2 . " c", "c.create_day = b.create_day")
            ->leftJoin($chatPopSql2 . " d", "d.create_day = b.create_day")
            ->select();
        foreach ($res as $k => $v) {
            $res[$k]["chat_pop"] = (string)$v["chat_pop"];
        }

        return $res;

    }

    // 访问百分比
    public static function perOfVis($date_arr = []): array
    {

        list($s, $d) = GetStrToTime($date_arr);
        $f = "";
        foreach (getVisitSource() as $k => $v) {
            $f .= "sum(if(source_name = '$v',1,0)) as $k ,";
        }

        $logSql = UrlSourceLog::field("$f count(source_name) as num")->where("source_name", "<>", "");
        if (!empty($date_arr)) {
            $logSql->whereBetween("create_time", [$s, $d]);
        }

        $res = $logSql->find();
        $resS = [];
        foreach ($res->toArray() as $k => $v) {

            if ($res->toArray()["num"] == 0) {
                $resS[] = ["type" => getVisitSource($k), "value" => 0];
            } else if ($k != "num") {
                $resS[] = ["type" => getVisitSource($k), "value" => $v / $res->toArray()["num"]];
            }
        }
        return $resS;
    }

    public static function sourceNum($date_arr = [])
    {
        list($s, $d) = GetStrToTime($date_arr);
//        print_r($dateArr);exit();
//        $s = strtotime($dateArr[0]);
//        $d = strtotime($dateArr[1]);


        // 今日访客
        $chatCount = ConnLog::whereBetweenTime("create_time", $s, $d)->where("type", 0)->group('no')->count();

        // 对话次数 与 有效对话
        $chatValidDataSql = ChatLog::field(" if (sum(is_valid) > 0,1,0) as chat_valid_count,create_day,talk_no")
            ->whereBetweenTime("create_time", $s, $d)->group("talk_no")->buildSql();

        $chatData = Db::table($chatValidDataSql)->alias("a")
            ->field("sum(chat_valid_count) as chat_valid_count,create_day,count(talk_no) as chat_count")->find();

        //今日对话人数
        $chatPop = Db::name("chat")->whereBetweenTime("update_time", $s, $d)->group("user_id")->count();

        // 回复对话
        $repCRe = StaffReplyCount::field("sum(if (reply=1,1,0 )) as reply_sum")->whereBetweenTime("create_time", $s, $d)->find();

        return [
            ["type" => "独立访客", "value" => $chatCount],
            ["type" => "对话次数", "value" => (integer)$chatData["chat_count"]],
            ["type" => "有效对话", "value" => (integer)$chatData["chat_valid_count"]],
            ["type" => "回复对话", "value" => (integer)$repCRe["reply_sum"]],
            ["type" => "对话人数", "value" => $chatPop]
        ];
    }


    public static function chatT($type, $date_arr = [], $valid_data, $page = 0, $limit = 10): array
    {
        $table = "ic_" . time() . mt_rand(10, 100);
        Db::startTrans();
        try {
            getDateSql($table, $date_arr);
            if ($type == "date") {
                $data = self::chatTLiDate($table, $type, $date_arr, $valid_data, $page, $limit);
            } else {
                $data = self::chatTLiStaff($table, $type, $date_arr, $valid_data, $page, $limit);
            }

            delDateSql($table);
            Db::commit();
        } catch (Exception $exception) {
            delDateSql($table);
            Db::rollback();

            return [];
        }
        return $data;
    }

    public static function chatTLiDate($table, $type, $date_arr = [], $valid_data, $page = 0, $limit = 10)
    {

        list($s, $d) = GetStrToTime($date_arr);

        // 在线时长
        $dutyArr = Duty::getServerData("af");
        $staffTimeStaffNo = Staff::where("duty_id", "in", $dutyArr)
            ->column("staff_no");

        // 在线时长
        $staffTimeLong = StaffTime::field("create_day,staff_no,sum(time_long) as time_long")
            ->group("create_day")
            ->whereBetweenTime("create_day", $s, $d)->where("staff_no", "in", $staffTimeStaffNo)->buildSql();

        // 对话时长
        $chatTimeLong = StaffChatTime::field("create_day,staff_no,sum(time_long) as time_long")
            ->group("create_day")
            ->whereBetweenTime("create_day", $s, $d)->buildSql();

        //  与 有效对话
        $chatValidDataSql = ChatLog::field(" if (sum(is_valid) > 0 ,1,0) as chat_valid_count,create_day,talk_no,staff_id")
            ->whereBetweenTime("create_day", $s, $d)->group("talk_no")->buildSql();

        $chatData = Db::table($chatValidDataSql)->alias("a")
            ->leftJoin("ic_staff s", "s.id = a.staff_id")
            ->field("sum(a.chat_valid_count) as chat_valid_count,a.create_day,count(a.talk_no) as chat_count,s.staff_no")
            ->group("a.create_day")->buildSql();

        // 最佳对话
        $field = "(if (sum(if(send_to = 'staff',1,0)) >= 10 && sum(if(send_to = 'user',1,0)) >= 10,1,0) )as staff_chat_goods_count,staff_id,create_day";
        $chatLog = ChatLog::field($field)->whereBetweenTime("create_day", $s, $d)
            ->group("user_id,talk_no")->buildSql();
        $chatLog2 = Db::table($chatLog)->alias("a")
            ->field("sum(a.staff_chat_goods_count) as staff_chat_goods_count,s.staff_no,a.create_day")
            ->leftJoin("ic_staff s", "s.id = a.staff_id")
            ->group("a.create_day")
            ->buildSql();

        // 回复对话次数
        $replyCo = StaffReplyCount::field("sum(reply) as reply,count(*) as reply_all,(sum(reply)/count(*)) as reply_ave,create_day,staff_no")
            ->whereBetweenTime("create_day", $s, $d)
            ->group("create_day")
            ->buildSql();

        $fie = "FROM_UNIXTIME(a.data_time,'%Y-%m-%d') as data_time
                ,b.time_long as now_staff_time_long
                ,c.time_long as now_chat_time_long
                ,d.chat_count,d.chat_valid_count
                ,e.staff_chat_goods_count
                ,sum(f.reply) as reply_num
                ,f.reply_all
                ,(f.reply_ave * 100) as reply_ave
                ";
        $f = " '合计' as data_time,
                sum(b.time_long) as now_staff_time_long,
                sum(c.time_long) as now_chat_time_long,
                sum(d.chat_count) as chat_count,
                sum(d.chat_valid_count) as chat_valid_count,
                sum(e.staff_chat_goods_count) as staff_chat_goods_count,
                sum(f.reply) as reply_num,
                sum(f.reply_all) as reply_all,
                (sum(f.reply_ave) * 100 / count(*)) as reply_ave";

        $all = Db::table($table)->alias("a")
            ->field($f)
            ->leftJoin($staffTimeLong . " b", "b.create_day = a.data_time")
            ->leftJoin($chatTimeLong . " c", "c.create_day = a.data_time")
            ->leftJoin($chatData . " d", "d.create_day = a.data_time")
            ->leftJoin($chatLog2 . " e", "e.create_day = a.data_time")
            ->leftJoin($replyCo . " f", "f.create_day = a.data_time")
            ->find();

        $all["ave_chat_time_long"] = round($all["now_chat_time_long"] / isField($all["chat_count"]), 2);
        $all["reply_ave"] = round($all["reply_ave"], 2);

        $res = Db::table($table)->alias("a")
            ->field($fie)
            ->leftJoin($staffTimeLong . " b", "b.create_day = a.data_time")
            ->leftJoin($chatTimeLong . " c", "c.create_day = a.data_time")
            ->leftJoin($chatData . " d", "d.create_day = a.data_time")
            ->leftJoin($chatLog2 . " e", "e.create_day = a.data_time")
            ->leftJoin($replyCo . " f", "f.create_day = a.data_time")
            ->group("a.data_time")
            ->order("a.data_time desc")
            ->where(function (Query $query)use ($valid_data){
                if ($valid_data=='valid')  $query->where('b.time_long','<>',"");
            })
            ->page($page, $limit)
            ->select();

        $resC = Db::table($table)->alias("a")
            ->field($fie)
            ->leftJoin($staffTimeLong . " b", "b.create_day = a.data_time")
            ->leftJoin($chatTimeLong . " c", "c.create_day = a.data_time")
            ->leftJoin($chatData . " d", "d.create_day = a.data_time")
            ->leftJoin($chatLog2 . " e", "e.create_day = a.data_time")
            ->leftJoin($replyCo . " f", "f.create_day = a.data_time")
            ->where(function (Query $query)use ($valid_data){
                if ($valid_data=='valid')  $query->where('b.time_long','<>',"");
            })
            ->group("a.data_time")->count();


        foreach ($res as $k => $v) {
            $res[$k]["ave_chat_time_long"] = round($v["now_chat_time_long"] / isField($v["chat_count"]), 2);
            foreach ($res[$k] as $k1 => $v1) {
                $res[$k][$k1] = isNUllNumber($v1);
            }
            $res[$k]["reply_ave"] = round($v["reply_ave"], 2);
        }
//        if ($valid_data == 'valid'){
//            $res = array_filter($res, function($item) {
//                foreach($item as $key=>$value) {
//                    if ($key=='staff_name') continue;
//                    if ($value != "0") {
//                        return true;
//                    }
//                }
//                return false;
//            });
//        }

        return [$res, $all, $resC];
    }


    public static function chatTLiStaff($table, $type, $date_arr = [], $valid_data, $page = 0, $limit = 10): array
    {
        list($s, $d) = GetStrToTime($date_arr);

        // 在线时长
        $staffTimeLong = StaffTime::field("create_day,staff_no,sum(time_long) as time_long")
            ->group("staff_no")
            ->whereBetweenTime("create_day", $s, $d)->buildSql();

        // 对话时长
        $chatTimeLong = StaffChatTime::field("create_day,staff_no,sum(time_long) as time_long")
            ->group("staff_no")
            ->whereBetweenTime("create_day", $s, $d)->buildSql();

        //  与 有效对话
        $chatValidDataSql = ChatLog::field(" if (sum(is_valid) > 0 ,1,0) as chat_valid_count,create_day,talk_no,staff_id")
            ->whereBetweenTime("create_day", $s, $d)->group("talk_no")->buildSql();

        $chatData = Db::table($chatValidDataSql)->alias("a")
            ->leftJoin("ic_staff s", "s.id = a.staff_id")
            ->field("sum(a.chat_valid_count) as chat_valid_count,a.create_day,count(a.talk_no) as chat_count,s.staff_no")
            ->group("a.staff_id")->buildSql();

        // 最佳对话
        $field = "(if (sum(if(send_to = 'staff',1,0)) >= 10 && sum(if(send_to = 'user',1,0)) >= 10,1,0) )as staff_chat_goods_count,staff_id,create_day";
        $chatLog = ChatLog::field($field)->whereBetweenTime("create_day", $s, $d)->group("user_id,talk_no")->buildSql();
        $chatLog2 = Db::table($chatLog)->alias("a")
            ->field("sum(a.staff_chat_goods_count) as staff_chat_goods_count,s.staff_no,a.create_day")
            ->leftJoin("ic_staff s", "s.id = a.staff_id")
            ->group("a.staff_id")
            ->buildSql();

        // 回复对话次数
        $replyCo = StaffReplyCount::field("sum(reply) as reply,count(*) as reply_all,(sum(reply)/count(*)) as reply_ave,create_day,staff_no")
            ->whereBetweenTime("create_day", $s, $d)
            ->group("staff_no")->buildSql();

        $fiew = "a.staff_name
                ,b.time_long as now_staff_time_long
                ,c.time_long as now_chat_time_long
                ,d.chat_count,d.chat_valid_count
                ,e.staff_chat_goods_count
                ,sum(f.reply) as reply_num
                ,f.reply_all
                ,(f.reply_ave * 100) as reply_ave
                ";

        $f = " '合计' as staff_name,
                sum(b.time_long) as now_staff_time_long,
                sum(c.time_long) as now_chat_time_long,
                sum(d.chat_count) as chat_count,
                sum(d.chat_valid_count) as chat_valid_count,
                sum(e.staff_chat_goods_count) as staff_chat_goods_count,
                sum(f.reply) as reply_num,
                sum(f.reply_all) as reply_all,
                (sum(f.reply_ave) * 100 / count(*) ) as reply_ave";

        $dutyArr = Duty::getServerData("af");
        $table = Staff::where("duty_id", "in", $dutyArr)->buildSql();

        $all = Db::table($table)->alias("a")
            ->field($f)
            ->leftJoin($staffTimeLong . " b", "b.staff_no = a.staff_no")
            ->leftJoin($chatTimeLong . " c", "c.staff_no = a.staff_no")
            ->leftJoin($chatData . " d", "d.staff_no = a.staff_no")
            ->leftJoin($chatLog2 . " e", "e.staff_no = a.staff_no")
            ->leftJoin($replyCo . " f", "f.staff_no = a.staff_no")
            ->find();
        $all["ave_chat_time_long"] = round($all["now_chat_time_long"] / isField($all["chat_count"]), 2);
        $all["reply_ave"] = round($all["reply_ave"], 2);

        $res = Db::table($table)->alias("a")
            ->field("$fiew")
            ->leftJoin($staffTimeLong . " b", "b.staff_no = a.staff_no")
            ->leftJoin($chatTimeLong . " c", "c.staff_no = a.staff_no")
            ->leftJoin($chatData . " d", "d.staff_no = a.staff_no")
            ->leftJoin($chatLog2 . " e", "e.staff_no = a.staff_no")
            ->leftJoin($replyCo . " f", "f.staff_no = a.staff_no")
            ->group("a.staff_no")
            ->where(function (Query $query)use ($valid_data){
              if ($valid_data=='valid')  $query->where('b.time_long','<>',"");
            })
            ->page($page, $limit)
            ->select();

        $resC = Db::table($table)->alias("a")
            ->field("$fiew")
            ->leftJoin($staffTimeLong . " b", "b.staff_no = a.staff_no")
            ->leftJoin($chatTimeLong . " c", "c.staff_no = a.staff_no")
            ->leftJoin($chatData . " d", "d.staff_no = a.staff_no")
            ->leftJoin($chatLog2 . " e", "e.staff_no = a.staff_no")
            ->leftJoin($replyCo . " f", "f.staff_no = a.staff_no")
            ->where(function (Query $query)use ($valid_data){
                if ($valid_data=='valid')  $query->where('b.time_long','<>',"");
            })
            ->group("a.staff_no")->count();

        foreach ($res as $k => $v) {
            $res[$k]["ave_chat_time_long"] = round($v["now_chat_time_long"] / isField($v["chat_count"]), 2);
            foreach ($res[$k] as $k1 => $v1) {
                $res[$k][$k1] = isNUllNumber($v1);
            }
            $res[$k]["reply_ave"] = round($v["reply_ave"], 2);
        }

//        if ($valid_data == 'valid'){
//            $res = array_filter($res, function($item) {
//                foreach($item as $key=>$value) {
//                    if ($key=='staff_name') continue;
//                    if ($value != "0") {
//                        return true;
//                    }
//                }
//                return false;
//            });
//        }

        return [$res, $all, $resC];
    }

    public static function chatTList($table, $type, $date_arr = [], $page = 0, $limit = 10): array
    {
        list($s, $d) = GetStrToTime($date_arr);

        // 在线时长
        $staffTimeLong = StaffTime::field("create_day,staff_no,sum(time_long) as time_long")
            ->group("create_day")
            ->whereBetweenTime("create_day", $s, $d)->buildSql();

        // 对话时长
        $chatTimeLong = StaffChatTime::field("create_day,staff_no,sum(time_long) as time_long")
            ->group("create_day")
            ->whereBetweenTime("create_day", $s, $d)->buildSql();

        //  与 有效对话
        $chatValidDataSql = ChatLog::field(" if (sum(is_valid) > 0 ,1,0) as chat_valid_count,create_day,talk_no,staff_id")
            ->whereBetweenTime("create_day", $s, $d)->group("talk_no")->buildSql();

        $chatData = Db::table($chatValidDataSql)->alias("a")
            ->leftJoin("ic_staff s", "s.id = a.staff_id")
            ->field("sum(a.chat_valid_count) as chat_valid_count,a.create_day,count(a.talk_no) as chat_count,s.staff_no")
            ->group("a.create_day")->buildSql();

        // 最佳对话
        $field = "(if (sum(if(send_to = 'staff',1,0)) >= 10 && sum(if(send_to = 'user',1,0)) >= 10,1,0) )as staff_chat_goods_count,staff_id,create_day";
        $chatLog = ChatLog::field($field)->whereBetweenTime("create_day", $s, $d)->group("user_id,talk_no")->buildSql();
        $chatLog2 = Db::table($chatLog)->alias("a")
            ->field("sum(a.staff_chat_goods_count) as staff_chat_goods_count,s.staff_no,a.create_day")
            ->leftJoin("ic_staff s", "s.id = a.staff_id")
            ->group("a.create_day")
            ->buildSql();

        // 回复对话次数
        $replyCo = StaffReplyCount::field("sum(reply) as reply,create_day,staff_no")
            ->whereBetweenTime("create_day", $s, $d)->group("create_day")->buildSql();

        $fie = "a.data_time
                ,b.time_long as now_staff_time_long
                ,c.time_long as now_chat_time_long
                ,d.chat_count,d.chat_valid_count
                ,e.staff_chat_goods_count
                ,sum(f.reply) as reply_num
                ,(sum(f.reply)/count(*)) as reply_ave
                ";

        $fiew = "a.staff_name
                ,b.time_long as now_staff_time_long
                ,c.time_long as now_chat_time_long
                ,d.chat_count,d.chat_valid_count
                ,e.staff_chat_goods_count
                ,sum(f.reply) as reply_num
                ,(sum(f.reply)/count(*)) as reply_ave
                ";

        $f = " '合计' as data_time,
                sum(b.time_long) as now_staff_time_long,
                sum(c.time_long) as now_chat_time_long,
                sum(d.chat_count) as chat_count,
                sum(d.chat_valid_count) as chat_valid_count,
                sum(e.staff_chat_goods_count) as staff_chat_goods_count,
                sum(f.reply) as reply_num,
                (sum(f.reply)/count(*)) as reply_ave";

        $all = Db::table($table)->alias("a")
            ->field($f)
            ->leftJoin($staffTimeLong . " b", "b.create_day = a.data_time")
            ->leftJoin($chatTimeLong . " c", "c.create_day = a.data_time")
            ->leftJoin($chatData . " d", "d.create_day = a.data_time")
            ->leftJoin($chatLog2 . " e", "e.create_day = a.data_time")
            ->leftJoin($replyCo . " f", "f.create_day = a.data_time")
            ->find();

        if ($type == "date") {
            $res = Db::table($table)->alias("a")
                ->field($fie)
                ->leftJoin($staffTimeLong . " b", "b.create_day = a.data_time")
                ->leftJoin($chatTimeLong . " c", "c.create_day = a.data_time")
                ->leftJoin($chatData . " d", "d.create_day = a.data_time")
                ->leftJoin($chatLog2 . " e", "e.create_day = a.data_time")
                ->leftJoin($replyCo . " f", "f.create_day = a.data_time")
                ->group("a.data_time")
                ->order("a.data_time desc")
                ->page($page, $limit)
                ->select();

            $resC = Db::table($table)->alias("a")
                ->field($fie)
                ->leftJoin($staffTimeLong . " b", "b.create_day = a.data_time")
                ->leftJoin($chatTimeLong . " c", "c.create_day = a.data_time")
                ->leftJoin($chatData . " d", "d.create_day = a.data_time")
                ->leftJoin($chatLog2 . " e", "e.create_day = a.data_time")
                ->leftJoin($replyCo . " f", "f.create_day = a.data_time")
                ->group("a.data_time")->count();
        } else {
            $dutyArr = Duty::getDataDepartment();
            $table = Staff::where("duty_id", "in", $dutyArr)->buildSql();
            $res = Db::table($table)->alias("a")
                ->field("$fiew")
                ->leftJoin($staffTimeLong . " b", "b.staff_no = a.staff_no")
                ->leftJoin($chatTimeLong . " c", "c.staff_no = a.staff_no")
                ->leftJoin($chatData . " d", "d.staff_no = a.staff_no")
                ->leftJoin($chatLog2 . " e", "e.staff_no = a.staff_no")
                ->leftJoin($replyCo . " f", "f.staff_no = a.staff_no")
                ->group("a.staff_no")
                ->order("a.create_time desc")
                ->page($page, $limit)
                ->select();
            $resC = Db::table($table)->alias("a")
                ->field("$fiew")
                ->leftJoin($staffTimeLong . " b", "b.staff_no = a.staff_no")
                ->leftJoin($chatTimeLong . " c", "c.staff_no = a.staff_no")
                ->leftJoin($chatData . " d", "d.staff_no = a.staff_no")
                ->leftJoin($chatLog2 . " e", "e.staff_no = a.staff_no")
                ->leftJoin($replyCo . " f", "f.staff_no = a.staff_no")
                ->group("a.staff_no")->count();
        }

        foreach ($res as $k => $v) {
            $res[$k]["ave_chat_time_long"] = $v["now_chat_time_long"] / isField($v["chat_count"]);
            foreach ($res[$k] as $k1 => $v1) {
                $res[$k][$k1] = isNUllNumber($v1);
            }
        }

        return [$res, $all, $resC];
    }

    public static function unreadChat($mobile)
    {

//        return Chat::where("last_info_status",0)
//            ->where("send_to","staff")
//            ->where("user_mobile",$mobile)->count();

        $countLog = "sum(if(cl.status = 0 and cl.send_to = 'user',1,0)) as count_log";
        $listSql = Chat::alias('a')
            ->field("a.id as chat_id,a.staff_id,a.staff_no,a.user_agent
            ,a.update_time
            ,if(a.last_info is null,'',a.last_info) as last_info
            ,a.last_info_status,a.user_no,a.send_to,a.collect,{$countLog}")
            ->leftjoin("ic_chat_log cl", "cl.chat_id = a.id")
            ->where("a.user_mobile", $mobile)
            ->find();
        if ($listSql) {
            return $listSql["count_log"];
        } else {
            return 0;
        }
    }
}